package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;
import model.Cliente;


/**
 *
 * @author Tiago
 */
public class DaoClientes {

    private PreparedStatement preparededstmt;
    private Statement stmt;
    public ResultSet resultSet;


    private ConnectionFactory conexao;
    private Connection con;
    private String sql;
    public ArrayList  bairros = new ArrayList();
    public ArrayList  cliente = new ArrayList();
    public int i;

    public DaoClientes() {
        this.i=-1;
        this.stmt = null;
        this.resultSet = null;
        this.conexao = ConnectionFactory.getInstance();
        this.con = ConnectionFactory.getConexao();

    }
    public PreparedStatement getPreparededstmt() {
        return preparededstmt;
    }

    public void setPreparededstmt(PreparedStatement preparededstmt) {
        this.preparededstmt = preparededstmt;
    }

    public Statement getStmt() {
        return stmt;
    }

    public void setStmt(Statement stmt) {
        this.stmt = stmt;
    }

    public ResultSet getResultSet() {
        return resultSet;
    }

    public void setResultSet(ResultSet resultSet) {
        this.resultSet = resultSet;
    }

    public ConnectionFactory getConexao() {
        return conexao;
    }

    public void setConexao(ConnectionFactory conexao) {
        this.conexao = conexao;
    }

    public Connection getCon() {
        return con;
    }

    public void setCon(Connection con) {
        this.con = con;
    }

    public String getSql() {
        return sql;
    }

    public void setSql(String sql) {
        this.sql = sql;
    }

    public  ResultSet retornaBairros() {
      
    try {
        this.sql = "SELECT * FROM tabBairros";
        Statement pstm;
        
             
      pstm = ConnectionFactory.getConexao().createStatement();  
      this.resultSet = pstm.executeQuery( this.sql);  
      return this.resultSet;

    }
    catch (SQLException ex) {
      System.out.println("SQLException: " + ex.getMessage());
      System.out.println("SQLState: " + ex.getSQLState());
      System.out.println("VendorError: " + ex.getErrorCode());
      return this.resultSet;
    }
    catch (Exception e) {
       
      System.out.println("Problemas ao tentar conectar com o banco de dados: " + e);
      return this.resultSet;
      
    }  

    }
    
    public ResultSet retornaCliente(String busca, int criterio) throws SQLException{

        
        //busca todos os registros quando não há parametros de busca
        if(busca.isEmpty()){
           
            this.sql = "SELECT Pk_intIdCliente as [Código], txtNomeCliente as [Nome],txtCpfCliente AS [CPF],  bitSituacaoCliente as [Situação] \n" +
                       "from tabCliente where  bitSituacaoCliente = 1";
        }
        //busca por nome
        if(!busca.isEmpty() && criterio== 1){
            
            busca = "Like '%"+busca+"%'";
        }       
         
       //buca por codigo 
       if(criterio== 0 && !busca.isEmpty())
       {
           
           this.sql = "SELECT Pk_intIdCliente as [Código], txtNomeCliente as [Nome],txtCpfCliente AS [CPF],  bitSituacaoCliente as [Situação]"
                   + " FROM tabCliente where PK_intIdCliente ="+busca+" And bitSituacaoCliente = 1";
           
       }
       //busca por os registros
       else if(criterio== 1 && !busca.isEmpty())
       {
          this.sql = "SELECT Pk_intIdCliente as [Código], txtNomeCliente as [Nome],txtCpfCliente AS [CPF],  bitSituacaoCliente as [Situação]"
                  + "FROM tabCliente e where txtNomeCliente "+busca +"and    bitSituacaoCliente = 1";
           
       }
       
       try {
              
           this.stmt = ConnectionFactory.getConexao().createStatement();  
           this.resultSet = this.stmt.executeQuery( this.sql);  
           return this.resultSet;
    
       }catch(Exception e){
           System.out.println(e);
                   }
          
        return this.resultSet;
          
    }
    public ResultSet retornaCliente(int idCliente){
            this.sql = "SELECT [Pk_intIdCliente] as [Código]\n" +
            "      ,[txtCpfCliente]\n" +
            "      ,[txtEnderecoCliente]\n" +
            "      ,[txtNomeCliente]\n" +
            "      ,[bitSituacaoCliente]\n" +
            "      ,CONVERT(varchar(20),dataNascimentoCliente,103) as [Data Convertida]\n" +
            "      ,[txtTelefoneCliente]\n" +
            "      ,[txtEnderecoComplemento]\n" +
            "      ,[Fk_intIdDependente]\n" +
            "      ,[txtEnderecoNum]\n" +
            "      ,[Fk_intIdBairro]\n" +
            "FROM tabCliente \n" +
            "WHERE Pk_intIdCliente ="+idCliente;
     System.out.println(this.sql);
        try {  
            this.stmt = ConnectionFactory.getConexao().createStatement();
            return this.resultSet = this.stmt.executeQuery( this.sql);
        } catch (SQLException ex) {
            Logger.getLogger(DaoClientes.class.getName()).log(Level.SEVERE, null, ex);
        }
  
           return null;
    }
    
    
     public ResultSet retornaUltimoClientes() throws SQLException{

        
            this.sql = "SELECT MAX(Pk_intIdCliente) AS [ULTIMO REGISTRO]"
                     + "From tabCliente  ";

       try {
              
           this.stmt = ConnectionFactory.getConexao().createStatement();  
           this.resultSet = this.stmt.executeQuery( this.sql);  
           return this.resultSet;
       
       }catch(Exception e){
           System.out.println(e);
                   }
          
        return this.resultSet;
          
    }
     
    public ResultSet retornaTodosCamposCliente(int idCliente) throws SQLException{
        this.sql = "SELECT [Pk_intIdCliente] as [Código]\n" +
        "      ,[txtCpfCliente]\n" +
        "      ,[txtEnderecoCliente]\n" +
        "      ,[txtNomeCliente]\n" +
        "      ,[bitSituacaoCliente]\n" +
        "      ,CONVERT(varchar(20),dataNascimentoCliente,103) as [Data Convertida]\n" +
        "      ,[txtTelefoneCliente]\n" +
        "      ,[txtEnderecoComplemento]\n" +
        "      ,[Fk_intIdDependente]\n" +
        "      ,[txtEnderecoNum]\n" +
        "      ,[Fk_intIdBairro]\n" +
        "FROM tabCliente \n" +
        "WHERE Pk_intIdCliente = "+idCliente;
        System.out.println(this.sql);
                
         this.stmt = ConnectionFactory.getConexao().createStatement();  
         this.resultSet = this.stmt.executeQuery( this.sql);  
         return this.resultSet;          
    }
    

    public void insereCliente(Cliente novoCliente) {
      
        String sqlInseCliente ="INSERT INTO [VIDEOLOCADORA].[dbo].[tabCliente]\n" +
        "           ([txtCpfCliente]\n" +
        "           ,[txtEnderecoCliente]\n" +
        "           ,[txtNomeCliente]\n" +
        "           ,[bitSituacaoCliente]\n" +
        "           ,[dataNascimentoCliente]\n" +
        "           ,[txtTelefoneCliente]\n" +
        "           ,[txtEnderecoComplemento]\n" +
        "           ,[Fk_intIdDependente]\n" +
        "           ,[txtEnderecoNum]\n" +
        "           ,[Fk_intIdBairro])\n" +

    
                "VALUES(?,?,?,?,?,?,?,?,?,?)";
        try {
           this.preparededstmt = ConnectionFactory.conexao.prepareStatement(sqlInseCliente);
           this.preparededstmt .setString(1, novoCliente.getTxtCpfCliente());
           
           this.preparededstmt .setString(2, novoCliente.getTxtEnderecoCliente());
           
           this.preparededstmt .setString(3, novoCliente.getTxtNomeCliente());
           
           this.preparededstmt .setInt(4, novoCliente.getBitSituacaoCliente());
           
           this.preparededstmt .setString(5,novoCliente.getDataNascimentoCliente());
           
           this.preparededstmt.setString(6, novoCliente.getTxtTelefoneCliente());
           
           this.preparededstmt.setString(7, novoCliente.getTxtEnderecoComplemento());
           this.preparededstmt.setObject(8, novoCliente.getFk_intIdDependente());
           this.preparededstmt .setString(9, novoCliente.getTxtEnderecoNum());
           
           this.preparededstmt .setInt(10, novoCliente.getFk_intIdBairro());
          
           this.preparededstmt.executeUpdate();
            
        } catch (SQLException e) {
            System.out.println("Erro ao inserir Cliente "+ e);

        }
       
    }
    public int updateCliente(int idCliente, Cliente editaCliente){
        
        this.sql="UPDATE [VIDEOLOCADORA].[dbo].[tabCliente]\n" +
        "   SET [txtCpfCliente] =" +
        "      ,[txtEnderecoCliente] = "+editaCliente.getTxtCpfCliente() +
        "      ,[txtNomeCliente] ="+editaCliente.getTxtNomeCliente() +
        "      ,[bitSituacaoCliente] = "+1 +
        "      ,[dataNascimentoCliente] = "+editaCliente.getDataNascimentoCliente() +
        "      ,[txtTelefoneCliente] = "+editaCliente.getTxtTelefoneCliente()+
        "      ,[txtEnderecoComplemento] ="+editaCliente.getTxtEnderecoCliente()+
        "      ,[Fk_intIdDependente] = "+null +
        "      ,[txtEnderecoNum] ="+editaCliente.getTxtEnderecoNum()+
        "      ,[Fk_intIdBairro] = "+editaCliente.getFk_intIdBairro()+
        " WHERE Pk_intIdCliente="+idCliente+
    "GO";
        try{
        this.preparededstmt = ConnectionFactory.conexao.prepareStatement(this.sql);
        return this.preparededstmt.executeUpdate();
        }catch (SQLException e){
            JOptionPane.showInternalConfirmDialog(null, "erro ao editar Cliente");
        }
        return 0;
        
    }
    public int excluirCliente(int idCliente){
        this.sql = "DELETE FROM [VIDEOLOCADORA].[dbo].[tabCliente]"
                + "WHERE Pk_intIdCliente = "+idCliente;
        try{
        this.preparededstmt = ConnectionFactory.conexao.prepareStatement(this.sql);
        this.preparededstmt.executeUpdate();
        return 1;
         }catch (SQLException e){
            JOptionPane.showInternalConfirmDialog(null, "erro ao editar Cliente");
            return 0;
        }
        
        
    }
    public ResultSet consultaHistoricoCliente(int idCliente){
        
        this.sql = "SELECT [Fk_intIdCliente] as [Cod.Cliente]\n" +
        "      ,[Fk_intIdLocacoes] as [Nº Locação] \n" +
        "  FROM [VIDEOLOCADORA].[dbo].[tabHistoricoLocacoes]\n" +
        "WHERE Fk_intIdCliente = "+idCliente;
        
        try {  
            this.stmt = ConnectionFactory.getConexao().createStatement();
             return this.resultSet = this.stmt.executeQuery( this.sql);
        } catch (SQLException ex) {
            Logger.getLogger(DaoClientes.class.getName()).log(Level.SEVERE, null, ex);
        }
        ;  
         return this.resultSet; 
   
    }
    
    
}
